package com.kcsj.dao;

import com.kcsj.domain.OrderAssProductModel;
import com.kcsj.domain.ProductModel;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;

/**
 * @author 傅立贤
 * @return rowsAffected 影响的行数
 * @throws SQLException
 */
public class OrderAssProductModelDao {
    private static OrderAssProductModelDao orderAssProductModelDao = new OrderAssProductModelDao();
    private OrderAssProductModelDao() {
    }
    public static OrderAssProductModelDao getInstance() {
        return orderAssProductModelDao;
    }

    public Collection<ProductModel> find(String orderNo, Connection conn) throws SQLException {
        Collection<ProductModel> productModelOfNumbers = new HashSet<>();
        String sql = "SELECT * from orderAssProductModel where orderNo = ?";
        //在连接上创建语句盒子对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,orderNo);
        //执行SQL语句
        ResultSet results = pstmt.executeQuery();
        while (results.next()) {
            String productModelNo = results.getString("productModelNo");
            Integer number = results.getInt("number");
            ProductModel productModel = ProductModelDao.getInstance().find(productModelNo, conn);
            productModel.setNumber(number);
            productModelOfNumbers.add(productModel);
        }
        return productModelOfNumbers;
    }

    public boolean update(OrderAssProductModel orderAssProductModel,Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String update = "update orderAssProductModel set OrderNo = ?,ProductModelNo= ?where OrderNo = " +
                orderAssProductModel.getOrder().getOrderNo();
        PreparedStatement pstmt = conn.prepareStatement(update);
        //执行SQL语句并返回结果和关闭连接
        pstmt.setString(1, orderAssProductModel.getOrder().getOrderNo());
        pstmt.setString(2, orderAssProductModel.getProductModel().getProductModelNo());
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }

    public boolean add(OrderAssProductModel orderAssProductModel,Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String create = "insert into OrderAssProductModel(orderNo,productModelNo,number) values (?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(create);
        //执行SQL语句并返回结果和关闭连接
        pstmt.setString(1, orderAssProductModel.getOrder().getOrderNo());
        pstmt.setString(2, orderAssProductModel.getProductModel().getProductModelNo());
        pstmt.setInt(3, orderAssProductModel.getNumber());
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }

    public boolean delete(Integer OrderNo,Connection conn) throws SQLException {
        //创建删除语句
        String delete = "delete from OrderAssProductModel where OrderNo = " + OrderNo;
        //在连接上创建语句盒子对象
        Statement statement = conn.createStatement();
        //执行SQL语句
        int rowsAffected = statement.executeUpdate(delete);
        //关闭连接
        JdbcHelper.close(statement,conn);
        return rowsAffected>0;
    }
}
